﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Configuration;
using System.Data.OleDb;
using System.Data;

namespace Project8
{
    /// <summary>
    /// Summary description for PosrednikServis
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class PosrednikServis : System.Web.Services.WebService
    {
        [WebMethod]
        public DataTable filtrirajPoBanka(string banka)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT Ime, Iznos, Kamata, BrojNaRati FROM Banki WHERE IME='" + banka + "'";
            OleDbCommand cmd = new OleDbCommand(select, conn);
            OleDbDataReader rid = cmd.ExecuteReader();
            DataTable tabela = new DataTable();
            tabela.Columns.Add("Ime na banka");
            tabela.Columns.Add("Iznos");
            tabela.Columns.Add("Kamata");
            tabela.Columns.Add("Rati");
            DataRow red;

            while (rid.Read())
            {
                red = tabela.NewRow();
                red.ItemArray = new string[4] { rid.GetString(0), rid.GetInt32(1).ToString(), rid.GetInt32(2).ToString(), rid.GetInt32(3).ToString() };
                tabela.Rows.Add(red);
            }

            conn.Close();
            return tabela;
        }

        [WebMethod]
        public DataTable filtrirajPoIznosPogolemOd(int iznos)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT Ime, Iznos, Kamata, BrojNaRati FROM Banki WHERE Iznos >'" + iznos + "'";
            OleDbCommand cmd = new OleDbCommand(select, conn);
            OleDbDataReader rid = cmd.ExecuteReader();
            DataTable tabela = new DataTable();
            tabela.Columns.Add("Ime na banka");
            tabela.Columns.Add("Iznos");
            tabela.Columns.Add("Kamata");
            tabela.Columns.Add("Rati");
            DataRow red;

            while (rid.Read())
            {
                red = tabela.NewRow();
                red.ItemArray = new string[4] { rid.GetString(0), rid.GetInt32(1).ToString(), rid.GetInt32(2).ToString(), rid.GetInt32(3).ToString() };
                tabela.Rows.Add(red);
            }

            conn.Close();
            return tabela;
        }

        [WebMethod]
        public DataTable filtrirajPoIznosPomalOd(int iznos)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT Ime, Iznos, Kamata, BrojNaRati FROM Banki WHERE Iznos<'" + iznos + "'";
            OleDbCommand cmd = new OleDbCommand(select, conn);
            OleDbDataReader rid = cmd.ExecuteReader();
            DataTable tabela = new DataTable();
            tabela.Columns.Add("Ime na banka");
            tabela.Columns.Add("Iznos");
            tabela.Columns.Add("Kamata");
            tabela.Columns.Add("Rati");
            DataRow red;

            while (rid.Read())
            {
                red = tabela.NewRow();
                red.ItemArray = new string[4] { rid.GetString(0), rid.GetInt32(1).ToString(), rid.GetInt32(2).ToString(), rid.GetInt32(3).ToString() };
                tabela.Rows.Add(red);
            }

            conn.Close();
            return tabela;
        }

        [WebMethod]
        public DataTable filtrirajPoKamataPogolemaOd(int kamata)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT Ime, Iznos, Kamata, BrojNaRati FROM Banki WHERE Kamata>'" + kamata + "'";
            OleDbCommand cmd = new OleDbCommand(select, conn);
            OleDbDataReader rid = cmd.ExecuteReader();
            DataTable tabela = new DataTable();
            tabela.Columns.Add("Ime na banka");
            tabela.Columns.Add("Iznos");
            tabela.Columns.Add("Kamata");
            tabela.Columns.Add("Rati");
            DataRow red;

            while (rid.Read())
            {
                red = tabela.NewRow();
                red.ItemArray = new string[4] { rid.GetString(0), rid.GetInt32(1).ToString(), rid.GetInt32(2).ToString(), rid.GetInt32(3).ToString() };
                tabela.Rows.Add(red);
            }

            conn.Close();
            return tabela;
        }

        [WebMethod]
        public DataTable filtrirajPoKamataPomalaOd(int kamata)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT Ime, Iznos, Kamata, BrojNaRati FROM Banki WHERE Kamata<'" + kamata + "'";
            OleDbCommand cmd = new OleDbCommand(select, conn);
            OleDbDataReader rid = cmd.ExecuteReader();
            DataTable tabela = new DataTable();
            tabela.Columns.Add("Ime na banka");
            tabela.Columns.Add("Iznos");
            tabela.Columns.Add("Kamata");
            tabela.Columns.Add("Rati");
            DataRow red;

            while (rid.Read())
            {
                red = tabela.NewRow();
                red.ItemArray = new string[4] { rid.GetString(0), rid.GetInt32(1).ToString(), rid.GetInt32(2).ToString(), rid.GetInt32(3).ToString() };
                tabela.Rows.Add(red);
            }

            conn.Close();
            return tabela;
        }

        public DataTable filtrirajPoBrojNaRatiPovekjeOd(int rati)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT Ime, Iznos, Kamata, BrojNaRati FROM Banki WHERE Rati>'" + rati + "'";
            OleDbCommand cmd = new OleDbCommand(select, conn);
            OleDbDataReader rid = cmd.ExecuteReader();
            DataTable tabela = new DataTable();
            tabela.Columns.Add("Ime na banka");
            tabela.Columns.Add("Iznos");
            tabela.Columns.Add("Kamata");
            tabela.Columns.Add("Rati");
            DataRow red;

            while (rid.Read())
            {
                red = tabela.NewRow();
                red.ItemArray = new string[4] { rid.GetString(0), rid.GetInt32(1).ToString(), rid.GetInt32(2).ToString(), rid.GetInt32(3).ToString() };
                tabela.Rows.Add(red);
            }

            conn.Close();
            return tabela;
        }

        [WebMethod]
        public DataTable filtrirajPoBrojNaRatiPomalkuOd(int rati)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT Ime, Iznos, Kamata, BrojNaRati FROM Banki WHERE Rati<'" + rati + "'";
            OleDbCommand cmd = new OleDbCommand(select, conn);
            OleDbDataReader rid = cmd.ExecuteReader();
            DataTable tabela = new DataTable();
            tabela.Columns.Add("Ime na banka");
            tabela.Columns.Add("Iznos");
            tabela.Columns.Add("Kamata");
            tabela.Columns.Add("Rati");
            DataRow red;

            while (rid.Read())
            {
                red = tabela.NewRow();
                red.ItemArray = new string[4] { rid.GetString(0), rid.GetInt32(1).ToString(), rid.GetInt32(2).ToString(), rid.GetInt32(3).ToString() };
                tabela.Rows.Add(red);
            }

            conn.Close();
            return tabela;
        }
        
        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

        [WebMethod]
        public void dodajKredit(String imeNaBanka, int iznos, int kamata, int brNaRati)
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string insert = "INSERT INTO Banki (Ime, Iznos, Kamata, BrojNaRati) VALUES (?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(insert, conn);
            cmd.Parameters.AddWithValue("?", imeNaBanka);
            cmd.Parameters.AddWithValue("?", iznos);
            cmd.Parameters.AddWithValue("?", kamata);
            cmd.Parameters.AddWithValue("?", brNaRati);
            cmd.ExecuteNonQuery();
            conn.Close();
        }

        [WebMethod]
        public string[] Banki()
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT IME FROM Banki";

            OleDbCommand cmd = new OleDbCommand(select, conn);

            OleDbDataReader rid = cmd.ExecuteReader();

            List<String> lista = new List<String>();

            while (rid.Read())
            {
                lista.Add(rid.GetString(0));
            }
            conn.Close();
            return lista.ToArray();

        }

        [WebMethod]
        public int[] Iznos()
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT Iznos FROM Banki";

            OleDbCommand cmd = new OleDbCommand(select, conn);

            OleDbDataReader rid = cmd.ExecuteReader();

            List<int> lista = new List<int>();

            while (rid.Read())
            {
                lista.Add(rid.GetInt32(0));
            }
            conn.Close();
            return lista.ToArray();

        }

        [WebMethod]
        public int[] Kamata()
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT Kamata FROM Banki";

            OleDbCommand cmd = new OleDbCommand(select, conn);

            OleDbDataReader rid = cmd.ExecuteReader();

            List<int> lista = new List<int>();

            while (rid.Read())
            {
                lista.Add(rid.GetInt32(0));
            }
            conn.Close();
            return lista.ToArray();

        }

        [WebMethod]
        public int[] BrRati()
        {
            string conString = ConfigurationManager.ConnectionStrings["konekcija"].ConnectionString;
            OleDbConnection conn = new OleDbConnection(conString);
            conn.Open();

            string select = "SELECT BrojNaRati FROM Banki";

            OleDbCommand cmd = new OleDbCommand(select, conn);

            OleDbDataReader rid = cmd.ExecuteReader();

            List<int> lista = new List<int>();

            while (rid.Read())
            {
                lista.Add(rid.GetInt32(0));
            }
            conn.Close();
            return lista.ToArray();

        }

        [WebMethod]
        public DataTable CelaTabela()
        {
            String[] ImeNaBanki = Banki();
            int[] IznosNaKreditot = Iznos();
            int[] KamataNaKreditot = Kamata();
            int[] BrojNaRatiNaKreditot = BrRati();


            DataTable tabela = new DataTable();
            tabela.Columns.Add("Ime na banka");
            tabela.Columns.Add("Iznos");
            tabela.Columns.Add("Kamata");
            tabela.Columns.Add("Rati");
            DataRow red = tabela.NewRow();
            for (int i = 0; i < ImeNaBanki.Length; i++)
            {
                red = tabela.NewRow();
                red.ItemArray = new string[4] { ImeNaBanki[i], IznosNaKreditot[i].ToString(), KamataNaKreditot[i].ToString(), BrojNaRatiNaKreditot[i].ToString() };
                tabela.Rows.Add(red);

            }

            return tabela;
        }

    }
}
